﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using Coldairarrow.Util.Excel.Util;
using NPOI.SS.UserModel;
using Coldairarrow.Util.Excel.Analyze;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.SS.Util;

namespace Coldairarrow.Util.Excel.App
{
    
    public class ExcelApp
    {

        /// <summary>
        /// 
        /// </summary>
        /// <param name="fileStream"></param>
        /// <param name="modelStream"></param>
        /// <param name="ruleJson"></param>
        /// <returns></returns>
        public static DataSet ImportExcel(FileStream fileStream, FileStream modelStream, string ruleJson)
        {
            if (fileStream == null)
            {
                throw new Exception("解析文件不能为空！");
            }
            else if (modelStream == null)
            {
                throw new Exception("模板文件不能为空！");
            }
            else if (ruleJson == null)
            {
                throw new Exception("解析规则流不能为空！");
            }
            else
            {
                IWorkbook modelWorkbook = ExcelUtil.GetWorkbook(modelStream);
                IWorkbook workbook = ExcelUtil.GetWorkbook(fileStream);
                if (workbook != null && modelWorkbook != null)
                {
                    ParseExcelRuleUtil parseExcelRuleUtil = new ParseExcelRuleUtil(ruleJson);
                    ExcelUtil.ValidateExcel(workbook, modelWorkbook, parseExcelRuleUtil);
                    ExcelDataAnalyzer analyzer = new ExcelDataAnalyzer(workbook);
                    DataSet dataSet = analyzer.Analyze(parseExcelRuleUtil);
                    return dataSet;
                }
                else
                {
                    throw new Exception("缺少Excel工作簿!");
                }
            }
        }
        public static void ImportCheckExcel(string filePath, string modelPath, string ruleJson)
        {
            if (filePath == null)
            {
                throw new Exception("解析文件不能为空！");
            }
            else if (modelPath == null)
            {
                throw new Exception("模板文件不能为空！");
            }
            else if (ruleJson == null)
            {
                throw new Exception("解析规则流不能为空！");
            }
            else
            {
                IWorkbook modelWorkbook = ExcelUtil.GetWorkbook(modelPath);
                IWorkbook workbook = ExcelUtil.GetWorkbook(filePath);
                if (workbook != null && modelWorkbook != null)
                {

                    ParseExcelRuleUtil parseExcelRuleUtil = new ParseExcelRuleUtil(ruleJson);
                    ExcelUtil.ValidateExcel(workbook, modelWorkbook, parseExcelRuleUtil);
                  
                }
                else
                {
                    throw new Exception("缺少Excel工作簿!");
                }
            }
        }
        public static DataSet ImportExcel(string filePath, string modelPath, string ruleJson)
        {
            if (filePath == null)
            {
                throw new Exception("解析文件不能为空！");
            }
            else if (modelPath == null)
            {
                throw new Exception("模板文件不能为空！");
            }
            else if (ruleJson == null)
            {
                throw new Exception("解析规则流不能为空！");
            }
            else
            {
                IWorkbook modelWorkbook = ExcelUtil.GetWorkbook(modelPath);
                IWorkbook workbook = ExcelUtil.GetWorkbook(filePath);
                if (workbook != null && modelWorkbook != null)
                {

                    ParseExcelRuleUtil parseExcelRuleUtil = new ParseExcelRuleUtil(ruleJson);
                    ExcelUtil.ValidateExcel(workbook, modelWorkbook, parseExcelRuleUtil);
                    ExcelDataAnalyzer analyzer = new ExcelDataAnalyzer(workbook);
                    DataSet dataSet = analyzer.Analyze(parseExcelRuleUtil);
                    return dataSet;
                }
                else
                {
                    throw new Exception("缺少Excel工作簿!");
                }
            }
        }

        public static Dictionary<string, List<List<string>>> ImportExcel(FileStream fileStream, Dictionary<string, int> startRowDictionary, Dictionary<string, int> endRowDictionary)
        {
            if (fileStream == null)
            {
                throw new Exception("解析文件不能为空！");
            }
            else
            {
                IWorkbook workbook = ExcelUtil.GetWorkbook(fileStream);
                if (workbook == null)
                {
                    throw new Exception("缺少Excel工作簿!");
                }
                else
                {
                    ExcelDataAnalyzer analyzer = new ExcelDataAnalyzer(workbook);
                    return analyzer.GetCommonList(workbook, startRowDictionary, endRowDictionary);
                }
            }
        }

        public static Dictionary<string, List<List<string>>> ImportExcel(string filePath, Dictionary<string, int> startRowDictionary, Dictionary<string, int> endRowDictionary)
        {
            if (filePath == null)
            {
                throw new Exception("解析文件不能为空！");
            }
            else
            {
                IWorkbook workbook = ExcelUtil.GetWorkbook(filePath);
                if (workbook == null)
                {
                    throw new Exception("缺少Excel工作簿!");
                }
                else
                {
                    ExcelDataAnalyzer analyzer = new ExcelDataAnalyzer(workbook);
                    return analyzer.GetCommonList(workbook, startRowDictionary, endRowDictionary);
                }
            }
        }

        public static List<List<string>> ImportExcel(FileStream fileStream, string sheetName, int startRow, int endRow)
        {
            if (fileStream == null)
            {
                throw new Exception("解析文件不能为空！");
            }
            else
            {
                IWorkbook workbook = ExcelUtil.GetWorkbook(fileStream);
                if (workbook == null)
                {
                    throw new Exception("缺少Excel工作簿!");
                }
                else
                {
                    ExcelDataAnalyzer analyzer = new ExcelDataAnalyzer(workbook);
                    return analyzer.Analyze(sheetName, startRow, endRow);
                }
            }
        }

        public static List<List<string>> ImportExcel(string filePath, string sheetName, int startRow, int endRow)
        {
            if (filePath == null)
            {
                throw new Exception("解析文件不能为空！");
            }
            else
            {
                IWorkbook workbook = ExcelUtil.GetWorkbook(filePath);
                if (workbook == null)
                {
                    throw new Exception("缺少Excel工作簿!");
                }
                else
                {
                    ExcelDataAnalyzer analyzer = new ExcelDataAnalyzer(workbook);
                    return analyzer.Analyze(sheetName, startRow, endRow);
                }
            }
        }

        public static DataSet ImportExcel(IWorkbook workbook, IWorkbook modelWorkbook, string ruleJson)
        {
            if (workbook == null)
            {
                throw new Exception("解析文件不能为空！");
            }
            else if (modelWorkbook == null)
            {
                throw new Exception("模板文件不能为空！");
            }
            else if (ruleJson == null)
            {
                throw new Exception("解析规则流不能为空！");
            }
            else
            {
              
                if (workbook != null && modelWorkbook != null)
                {
                    ParseExcelRuleUtil parseExcelRuleUtil = new ParseExcelRuleUtil(ruleJson);
                    ExcelUtil.ValidateExcel(workbook, modelWorkbook, parseExcelRuleUtil);
                    ExcelDataAnalyzer analyzer = new ExcelDataAnalyzer(workbook);
                    DataSet dataSet = analyzer.Analyze(parseExcelRuleUtil);
                    return dataSet;
                }
                else
                {
                    throw new Exception("缺少Excel工作簿!");
                }
            }
        }

        public static Dictionary<string, List<List<string>>> ImportExcel(IWorkbook workbook, Dictionary<string, int> startRowDictionary, Dictionary<string, int> endRowDictionary)
        {
            if (workbook == null)
            {
                throw new Exception("解析文件不能为空！");
            }
            else
            {
                if (workbook == null)
                {
                    throw new Exception("缺少Excel工作簿!");
                }
                else
                {
                    ExcelDataAnalyzer analyzer = new ExcelDataAnalyzer(workbook);
                    return analyzer.GetCommonList(workbook, startRowDictionary, endRowDictionary);
                }
            }
        }

        public static List<List<string>> ImportExcel(IWorkbook workbook, string sheetName, int startRow, int endRow)
        {
            if (workbook == null)
            {
                throw new Exception("解析文件不能为空！");
            }
            else
            {
                ExcelDataAnalyzer analyzer = new ExcelDataAnalyzer(workbook);
                return analyzer.Analyze(sheetName, startRow, endRow);
            }
        }

        /*
        public static List<string> GetSheetNames(ImportContext context)
        {
            return GetWorkbookSheetNames(context.GetFileWorkbook());
        }

        public static List<string> GetModelSheetNames(ImportContext context)
        {
            return GetWorkbookSheetNames(context.GetModelWorkbook());
        }
        */

        public static List<string> GetSheetNames(FileStream fileStream)
        {
            if (fileStream == null)
            {
                throw new Exception("解析文件不能为空！");
            }
            else
            {
                IWorkbook workbook = ExcelUtil.GetWorkbook(fileStream);
                if (workbook == null)
                {
                    throw new Exception("缺少Excel工作簿!");
                }
                else
                {
                    return GetWorkbookSheetNames(workbook);
                }
            }
        }

        public static List<string> GetSheetNames(string filePath)
        {
            if (filePath == null)
            {
                throw new Exception("解析文件不能为空！");
            }
            else
            {
                IWorkbook workbook = ExcelUtil.GetWorkbook(filePath);
                if (workbook == null)
                {
                    throw new Exception("缺少Excel工作簿!");
                }
                else
                {
                    return GetWorkbookSheetNames(workbook);
                }
            }
        }


        private static List<string> GetWorkbookSheetNames(IWorkbook workbook)
        {
            List<string> sheetNames = new List<string>();
            if (workbook == null)
            {
                return sheetNames;
            }
            else
            {
                int count = workbook.NumberOfSheets;

                for (int i = 0; i < count; ++i)
                {
                    ISheet sheet = workbook.GetSheetAt(i);
                    if (sheet != null)
                    {
                        sheetNames.Add(sheet.SheetName);
                    }
                }
                return sheetNames;
            }
        }

        public static List<string> GetSame(List<string> modelNames, List<string> importNames)
        {
            return GlobalUtil.GetSame(modelNames, importNames);
        }

        public static List<string> GetDifferent(List<string> modelNames, List<string> importNames)
        {
            return GlobalUtil.GetDifferent(modelNames, importNames);
        }

        public static Dictionary<string, List<string>> GetDifferentList(List<string> modelNames, List<string> importNames)
        {
            return GlobalUtil.GetDifferentList(modelNames, importNames);
        }

        public static byte[] ExportExcel(DataSet dataSet, string excelType)
        {
            byte[] byteArray = null;

            if (dataSet != null && dataSet.Tables.Count >= 1)
            {
                IWorkbook workbook = null;
                if (string.IsNullOrEmpty(excelType))
                {
                    throw new Exception("Excel文件类型不能为空！");
                }
                else if (!".xls".Equals(excelType) && !".xlsx".Equals(excelType))
                {
                    throw new Exception("Excel文件类型错误！");
                }
                else
                {
                    if (".xlsx".Equals(excelType))
                    {
                        workbook = new XSSFWorkbook();
                    }
                    else
                    {
                        workbook = new HSSFWorkbook();
                    }

                    ICellStyle headCellStyle = ExcelUtil.SetCellStyle((IWorkbook)workbook, true);
                    ICellStyle cellStyle = ExcelUtil.SetCellStyle((IWorkbook)workbook, false);
                    foreach (DataTable dataTable in dataSet.Tables)
                    {

                        ISheet sheet = ((IWorkbook)workbook).CreateSheet(dataTable.TableName);
                        sheet.DefaultColumnWidth = 10;

                        //设置列名
                        for (int i = 0; i < dataTable.Columns.Count; i++)
                        {
                            DataColumn dataColumn = (DataColumn)dataTable.Columns[i];
                            if (dataColumn != null && !string.IsNullOrEmpty(dataColumn.ColumnName))
                            {

                                string[] value = dataColumn.ColumnName.Split(new char[] { '.' });
                                if (value != null && value.Length >= 1)
                                {
                                    for (int j = 0; j < value.Length; ++j)
                                    {
                                        value[j] = value[j].Replace("$$$", ".");
                                        IRow row = sheet.GetRow(j);
                                        if (row == null)
                                        {
                                            row = sheet.CreateRow(j);
                                        }

                                        ICell cell = row.GetCell(i);
                                        if (cell == null)
                                        {
                                            cell = row.CreateCell(i);
                                        }

                                        cell.SetCellValue(value[j]);
                                        cell.CellStyle = headCellStyle;
                                    }
                                }
                            }
                        }

                        //设置列的合并单元格
                        int headMaxRowNum = ExcelUtil.GetHeadMaxRowNum(dataTable);
                        System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
                        while (rows.MoveNext())
                        {
                            IRow row = (IRow)rows.Current;
                            for (int i = 0; i < row.LastCellNum; i++)
                            {
                                ICell cell = row.GetCell(i);
                                if (cell != null && !string.IsNullOrEmpty(ExcelUtil.GetCellStringValue(cell)))
                                {
                                    int j = ExcelUtil.GetMergeRow(cell, sheet, i);
                                    int mergeColumn = ExcelUtil.GetMergeColumn(cell, sheet);
                                    if (j > 1 || mergeColumn > 1)
                                    {
                                        CellRangeAddress cellRangeAddress = new CellRangeAddress(cell.RowIndex, cell.RowIndex + j - 1, cell.ColumnIndex, cell.ColumnIndex + mergeColumn - 1);
                                        sheet.AddMergedRegion(cellRangeAddress);
                                        ExcelUtil.SetBodyStyle(headCellStyle, cellRangeAddress, sheet);
                                    }
                                }
                            }
                        }

                        //填充数据
                        for (int index = 0; index < dataTable.Rows.Count; index++)
                        {
                            DataRow dataRow = dataTable.Rows[index];
                            IRow row = sheet.GetRow(headMaxRowNum + index);
                            if (row == null)
                            {
                                row = sheet.CreateRow(headMaxRowNum + index);
                            }

                            for (int j = 0; j < dataTable.Columns.Count; j++)
                            {
                                string columnName = dataTable.Columns[j].ColumnName;
                                if (!string.IsNullOrEmpty(columnName))
                                {
                                    ICell cell = row.GetCell(j);
                                    if (cell == null)
                                    {
                                        cell = row.CreateCell(j);
                                    }

                                    if (dataRow[columnName] != null)
                                    {
                                        string value = dataRow[columnName].ToString();
                                        cell.SetCellValue(value);
                                    }
                                    cell.CellStyle = cellStyle;
                                }
                            }
                        }
                    }

                    //将excel工作薄序列化成 byte数组
                    MemoryStream workStream = new MemoryStream();
                 
                    workbook.Write(workStream,true);
                    byteArray = workStream.ToArray();
                }
            }
            else
            {
                throw new Exception("数据集不能为空！");
            }
            return byteArray;
        }
    }
}
